/*
Navicat MySQL Data Transfer

Source Server         : Steavy
Source Server Version : 50519
Source Host           : localhost:3306
Source Database       : testsystem

Target Server Type    : MYSQL
Target Server Version : 50519
File Encoding         : 65001

Date: 2012-04-20 11:35:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `admininfo`
-- ----------------------------
DROP TABLE IF EXISTS `admininfo`;
CREATE TABLE `admininfo` (
  `UID` bigint(20) NOT NULL,
  `Description` text,
  PRIMARY KEY (`UID`),
  CONSTRAINT `admininfo_ibfk_1` FOREIGN KEY (`UID`) REFERENCES `generalinfo` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of admininfo
-- ----------------------------
INSERT INTO `admininfo` VALUES ('2', 'headmaster');

-- ----------------------------
-- Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `CourseOwner` bigint(20) NOT NULL,
  `CourseID` int(11) NOT NULL AUTO_INCREMENT,
  `CourseName` varchar(60) NOT NULL,
  PRIMARY KEY (`CourseID`),
  KEY `CourseID` (`CourseID`),
  KEY `CourseOwner` (`CourseOwner`),
  CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`CourseOwner`) REFERENCES `admininfo` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('2', '1', 'PZ');
INSERT INTO `courses` VALUES ('2', '13', 'someCourse');

-- ----------------------------
-- Table structure for `generalinfo`
-- ----------------------------
DROP TABLE IF EXISTS `generalinfo`;
CREATE TABLE `generalinfo` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Login` varchar(100) CHARACTER SET latin1 NOT NULL,
  `Password` varchar(100) CHARACTER SET latin1 NOT NULL,
  `PermID` tinyint(4) NOT NULL,
  `FirstName` varchar(100) CHARACTER SET latin1 NOT NULL,
  `LastName` varchar(100) CHARACTER SET latin1 NOT NULL,
  `RegDate` date NOT NULL,
  `IsLoggedIn` bit(1) NOT NULL,
  PRIMARY KEY (`UID`),
  KEY `PermID` (`PermID`),
  CONSTRAINT `generalinfo_ibfk_1` FOREIGN KEY (`PermID`) REFERENCES `permissions` (`PermID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=cp850;

-- ----------------------------
-- Records of generalinfo
-- ----------------------------
INSERT INTO `generalinfo` VALUES ('1', 'steavy', '79054', '2', 'Stepan', 'Tyzhaj', '2012-03-22', '');
INSERT INTO `generalinfo` VALUES ('2', 'admin', '123456', '1', 'Petro', 'Vahin', '2012-03-24', '');

-- ----------------------------
-- Table structure for `groups`
-- ----------------------------
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `GroupID` int(11) NOT NULL AUTO_INCREMENT,
  `GroupName` varchar(50) NOT NULL,
  PRIMARY KEY (`GroupID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of groups
-- ----------------------------
INSERT INTO `groups` VALUES ('1', 'PMI-32');
INSERT INTO `groups` VALUES ('3', 'PMI-33');
INSERT INTO `groups` VALUES ('8', 'PMI-31');

-- ----------------------------
-- Table structure for `permissions`
-- ----------------------------
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `PermID` tinyint(4) NOT NULL,
  `Status` varchar(100) NOT NULL,
  PRIMARY KEY (`PermID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of permissions
-- ----------------------------
INSERT INTO `permissions` VALUES ('1', 'Admin');
INSERT INTO `permissions` VALUES ('2', 'User');

-- ----------------------------
-- Table structure for `tests`
-- ----------------------------
DROP TABLE IF EXISTS `tests`;
CREATE TABLE `tests` (
  `TestID` int(11) NOT NULL AUTO_INCREMENT,
  `TestName` varchar(50) NOT NULL DEFAULT 'name',
  `TestPath` varchar(300) NOT NULL COMMENT '"/Models/Tests/"+FirstName+LastName+"/"+CourseName+"/"+TestName+".xml"',
  PRIMARY KEY (`TestID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tests
-- ----------------------------
INSERT INTO `tests` VALUES ('3', 'test3', '/Models/Tests/PetroVahin/PZ/test3.xml');
INSERT INTO `tests` VALUES ('4', 'simpleTest.xml', '/Models/Tests/PetroVahin/someCourse/simpleTest.xml');

-- ----------------------------
-- Table structure for `testsforgroups`
-- ----------------------------
DROP TABLE IF EXISTS `testsforgroups`;
CREATE TABLE `testsforgroups` (
  `TestID` int(11) NOT NULL,
  `GroupID` int(11) NOT NULL,
  `ExpirationDate` datetime NOT NULL,
  PRIMARY KEY (`TestID`,`GroupID`),
  KEY `GroupID` (`GroupID`),
  CONSTRAINT `testsforgroups_ibfk_1` FOREIGN KEY (`TestID`) REFERENCES `tests` (`TestID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `testsforgroups_ibfk_2` FOREIGN KEY (`GroupID`) REFERENCES `groups` (`GroupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of testsforgroups
-- ----------------------------
INSERT INTO `testsforgroups` VALUES ('3', '1', '2012-08-08 14:46:47');
INSERT INTO `testsforgroups` VALUES ('4', '1', '2012-07-21 11:31:55');

-- ----------------------------
-- Table structure for `testsincourse`
-- ----------------------------
DROP TABLE IF EXISTS `testsincourse`;
CREATE TABLE `testsincourse` (
  `CourseID` int(11) NOT NULL,
  `TestID` int(11) NOT NULL,
  PRIMARY KEY (`CourseID`,`TestID`),
  KEY `TestID` (`TestID`),
  KEY `CourseID` (`CourseID`),
  CONSTRAINT `testsincourse_ibfk_1` FOREIGN KEY (`TestID`) REFERENCES `tests` (`TestID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `testsincourse_ibfk_2` FOREIGN KEY (`CourseID`) REFERENCES `courses` (`CourseID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of testsincourse
-- ----------------------------
INSERT INTO `testsincourse` VALUES ('1', '3');
INSERT INTO `testsincourse` VALUES ('13', '4');

-- ----------------------------
-- Table structure for `testsresults`
-- ----------------------------
DROP TABLE IF EXISTS `testsresults`;
CREATE TABLE `testsresults` (
  `UID` bigint(20) NOT NULL,
  `PassingDate` datetime NOT NULL,
  `Result` int(11) NOT NULL,
  `TestID` int(11) NOT NULL,
  PRIMARY KEY (`UID`,`PassingDate`),
  KEY `TestID` (`TestID`),
  CONSTRAINT `testsresults_ibfk_1` FOREIGN KEY (`UID`) REFERENCES `userinfo` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `testsresults_ibfk_2` FOREIGN KEY (`TestID`) REFERENCES `tests` (`TestID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of testsresults
-- ----------------------------
INSERT INTO `testsresults` VALUES ('1', '2012-03-31 00:23:29', '4', '3');
INSERT INTO `testsresults` VALUES ('1', '2012-03-31 00:25:03', '3', '3');
INSERT INTO `testsresults` VALUES ('1', '2012-03-31 00:25:17', '1', '3');
INSERT INTO `testsresults` VALUES ('1', '2012-04-01 17:46:15', '0', '3');
INSERT INTO `testsresults` VALUES ('1', '2012-04-20 11:34:05', '1', '4');

-- ----------------------------
-- Table structure for `userinfo`
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `UID` bigint(20) NOT NULL,
  `GroupID` int(11) NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY (`UID`),
  KEY `GroupID` (`GroupID`),
  CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`UID`) REFERENCES `generalinfo` (`UID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `userinfo_ibfk_2` FOREIGN KEY (`GroupID`) REFERENCES `groups` (`GroupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', '1', 'steavy29 (=');
